// src/sqlite/index.js
import { app } from 'electron'
import path from 'path'
import fs from 'fs'
import initSqlJs from 'sql.js'

const userDataPath = app.getPath('userData')
const dbPath = path.join(userDataPath, 'sqliteDatabase.db')

console.log('Database path:', dbPath)

class Database {
  constructor() {
    this.db = null
  }

  async open() {
    try {
      const SQL = await initSqlJs()

      let dbData = null

      if (fs.existsSync(dbPath)) {
        const fileBuffer = fs.readFileSync(dbPath)
        dbData = new Uint8Array(fileBuffer)
      }

      this.db = new SQL.Database(dbData || undefined)
      console.log('Connected to the database.')
    } catch (err) {
      console.error('Error opening database:', err)
      throw err
    }
  }

  close() {
    if (this.db) {
      this.db.close()
      console.log('Database closed.')
    }
    return Promise.resolve()
  }

  async save() {
    if (!this.db) {
      throw new Error('Database not initialized')
    }

    try {
      const data = this.db.export()
      const buffer = Buffer.from(data)
      fs.writeFileSync(dbPath, buffer)
      console.log('Database saved to file.')
    } catch (err) {
      console.error('Error saving database:', err)
      throw err
    }
  }

  async query(param) {
    console.log('=== SELECT方法被调用 ===')
    console.log(param)
    if (!this.db) {
      throw new Error('Database not initialized')
    }

    try {
      const stmt = this.db.prepare(param.sql)
      if (param.params) {
        stmt.bind(param.params)
      }

      const rows = []
      while (stmt.step()) {
        rows.push(stmt.getAsObject())
      }
      stmt.free()

      return rows
    } catch (err) {
      console.error('Query error:', err)
      throw err
    }
  }

  async insert(param) {
    console.log('=== ADD方法被调用 ===')

    if (!this.db) {
      throw new Error('Database not initialized')
    }

    try {
      const keys = Object.keys(param.data)
      const values = Object.values(param.data)
      const placeholders = keys.map(() => '?').join(',')
      const sql = `INSERT INTO ${param.table} (${keys.join(',')}) VALUES (${placeholders})`

      const stmt = this.db.prepare(sql)
      stmt.bind(values)
      stmt.step()
      stmt.free()

      const result = this.db.exec('SELECT last_insert_rowid() as lastID')
      const lastID =
        (result[0] && result[0].values && result[0].values[0] && result[0].values[0][0]) || null

      // 直接保存
      await this.save()

      return lastID
    } catch (err) {
      console.error('Insert error:', err)
      throw err
    }
  }

  async update(param) {
    console.log('=== UPDATE方法被调用 ===')
    if (!this.db) {
      throw new Error('Database not initialized')
    }

    try {
      const entries = Object.entries(param.data)
        .map(([key]) => `${key} = ?`)
        .join(',')
      const values = Object.values(param.data)
      const sql = `UPDATE ${param.table} SET ${entries} WHERE ${param.condition}`

      const stmt = this.db.prepare(sql)
      stmt.bind(values)
      stmt.step()
      const changes = this.db.getRowsModified()
      stmt.free()

      // 直接保存
      await this.save()

      return changes
    } catch (err) {
      console.error('Update error:', err)
      throw err
    }
  }

  async delete(param) {
    console.log('=== DELETE方法被调用 ===')
    console.log('参数:', param)

    if (!this.db) {
      console.error('数据库未初始化')
      throw new Error('Database not initialized')
    }

    try {
      const sql = `DELETE FROM ${param.table} WHERE ${param.condition}`

      console.log('执行删除SQL:', sql) // 添加日志

      const stmt = this.db.prepare(sql)
      stmt.step()
      const changes = this.db.getRowsModified()
      stmt.free()

      console.log('受影响的行数:', changes) // 添加日志

      // 直接保存
      await this.save()

      return changes
    } catch (err) {
      console.error('Delete error:', err)
      throw err
    }
  }
}

const db = new Database()

export const initSqlite = async () => {
  try {
    await db.open()

    await db.query({ sql: 'PRAGMA foreign_keys = ON' })

    await db.query({
      sql: `
      CREATE TABLE IF NOT EXISTS project (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT
      )`
    })

    await db.query({
      sql: `
      CREATE TABLE IF NOT EXISTS time (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        projectId INTEGER,
        content TEXT,
        date TEXT,
        time TEXT
      )`
    })

    await db.query({
      sql: `
      CREATE TABLE IF NOT EXISTS password (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        projectId INTEGER,
        username TEXT,
        password TEXT,
        tip TEXT
      )`
    })

    await db.save()

    console.log('Database initialized.')
  } catch (err) {
    console.error('Error initializing database:', err)
    throw err
  }
}

export const saveDatabase = async () => {
  try {
    await db.save()
  } catch (err) {
    console.error('Error saving database:', err)
  }
}

export const sqQuery = db.query.bind(db)
export const sqInsert = db.insert.bind(db)
export const sqUpdate = db.update.bind(db)
export const sqDelete = db.delete.bind(db)
export const sqlSave = db.save.bind(db)
